Getting the most with SQL*Plus

By Garrett J. Suhm

SQL*Plus is the Rodney Dangerfield of Oracle products. People probably use it millions of times daily to perform useful work, but it gets no respect. Admit it--you've been using the tool all these years without taking advantage of its capabilities. Well, if you just give us five minutes a day for four weeks, we'll get your abs in.... Well, maybe we can't fix flabby abs, but at least we can make your use of SQL*Plus more effective.

The ORACLE.INI and you

The first mistake most people make is not taking advantage of the customization options available for SQL*Plus. The Windows 3.1 home directory includes the file WIN.INI, which contains the customization for most Windows products. Installation of the Oracle products added a line to the WIN.INI similar to the following:

[Oracle]
ORA_CONFIG=c:\windows\oracle.ini

You can place customizations specific to Oracle within the ORACLE.INI file. For example, if you're still entering a string for the database name (like DB_TCP) when you connect, then you can add the following line to the ORACLE.INI file:

LOCAL=DB_TCP

This will allow you to connect without specifying the database connect string again. Of course, you can still point to other databases. The ORACLE.INI file contains a section of special interest to SQL*Plus users. This section holds the parameters that control where SQL*Plus looks for various files. The default installation will probably look like this:

[plus31]
SQLPATH=C:\ORAWIN\dbs
PLUS31=C:\ORAWIN\PLUS31

The PLUS31 entry controls where SQL*Plus looks for help scripts and the global startup script file called GLOGIN.SQL. You probably won't need to change this. If you're using SQL*Plus in a networked environment, you can make changes to GLOGIN.SQL that all users will see.

The SQLPATH statement determines where SQL*Plus searches for SQL files. We changed ours to the following:

SQLPATH=C:\ORAWIN\dbs;c:\utility\sql

SQL*Plus will now search for our SQL files first in the working directory, then in the default installation directory, and finally in a central directory in which we've stored common useful scripts. Note that the ORACLE.INI files applies to Windows 3. Both Windows 95 and Windows NT versions of SQL*Plus (as well as the other 32-bit Oracle products) use the registry editor. These settings will still be identical, but can be found under the "Oracle" heading under "Software" in the registry.

Start me up

GLOGIN.SQL isn't the only file executed at startup. If you have a LOGIN.SQL file in your working directory, this will be executed also. You can put any valid SQL*Plus command (including SQL queries) in these files, and they will be executed every time you run the tool. The global file is executed first, so you can override settings in the LOGIN.SQL file as needed. Make sure that that you press [Return] after the last command in these files, or you will see something like this:

Input truncated to 29 characters

You may want to put many settings in these files. Suppose you hate the Notepad. Try changing your editor to your personal favorite:

define _editor=brief

You can also define common column names to more manageable sizes:

column description format a25
column table_name format a20
column salary format $999,999.99

The bottom line is that if you find your-self repeatedly typing the same command, you should probably add it to either your LOGIN.SQL file or your GLOGIN.SQL file. And if you're repeatedly typing in the same SQL statements, save them to a file, and place them in a common directory pointed to by SQLPATH.

The SET command

One of the more useful commands in SQL*Plus is the SET command. There are over 50 settings that you can alter to change your SQL*Plus environment. We'll touch on some of the more interesting ones. For example, if you use the RDBMS_ OUTPUT package for I/O from stored procedures, you could use this line to ensure you see the output:

set SERVEROUTPUT ON

If you have a lot of Oracle version 6 scripts lying around, or if you still use Oracle CASE version 5, the following line will ensure that you don't create CHAR fields when you really want VARCHAR2:

set COMPATIBILITY V6

If you perform a lot of queries that return a large number of rows, you can increase your query efficiency by setting the array size higher than its default of 20:

set ARRAYSIZE 100

Remember that you'll use more client memory if you increase this parameter. Also, Oracle allows values of up to 1,000 but has stated that values beyond 100 have a minimal performance benefit. If you would like to echo all the commands that are executed, use the line

set ECHO ON

By default, SQL*Plus will show the number of rows a query will return if six or more records are selected. You can turn this off, turn it on, or change the record threshold to any number of records by using the following commands:

set FEEDBACK OFF
set FEEDBACK ON
set FEEDBACK x

If you want to eliminate I/O to your display device to improve performance during batch jobs, issue the command

set FLUSH OFF

Exit stage right

If you use SQL*Plus for batch processing, it's often useful to return a code to the operating system showing whether SQL*Plus was successful. You have the capability to control the handling of both SQL errors and operating system-specific errors (such as disk full, no privileges) by using the following commands

WHENEVER SQLERROR <action>
WHENEVER OSERROR <action>

where <action> is one of the following values:

• CONTINUE--Ignore error and continue processing
• COMMIT--Commit outstanding changes to the database
• ROLLBACK--Roll back all outstanding changes to the database
• EXIT <exit action>--SQL*Plus will exit as soon as the error message prints. It will also return a value to the operating system, depending on the <exit action>.
• SUCCESS--Exits to the operating system normally
• FAILURE--Exits with an operating system return code of failure
• WARNING--Exits with an operating system return code of warning
• variable--You can pass any SQL*Plus variable as a value (or integer) to the operating system.

These are just a few of the customizations you can make to SQL*Plus that you can use to improve your daily routine. It may not cure those flabby abs, but it should make working with Oracle easier.

Garrett Suhm is a senior consultant for Tactics, Inc., a firm providing Oracle-based solutions for customers across the Southeast. If you have any questions about this article, you can reach Garrett by phone at (770) 673-1254 or via E-mail at gsuhm@tacticsus.com.

[The Cobb Group Home Page]

Copyright (c) 1996 The Cobb Group, a division of Ziff-Davis Publishing Company. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of Ziff-Davis Publishing Company is prohibited. The Cobb Group and The Cobb Group logo are trademarks of Ziff-Davis Publishing Company.